import time

import requests,random,json
import demjson
from pyquery import PyQuery as pq

from com.utils import dbutil
from com.lechebang.Setting import *
from com.lechebang.StrAnalyse import StrAnalyse


def parse(city,htmldata):
    dealerHtml=htmldata('ul.list-box .list-item').items()
    dealers=[]
    for item in dealerHtml:
        dealer={}
        dealer['city']=city
        info=item('.info-wrap')
        dealer['id']=item.attr('id')
        dealer['name']=info('li:first-child a span').text()
        dealer['medal'] = info('li:first-child span.icon-medal i').attr('class')
        dealer['brand']=info('li:nth-child(2) span em').text()
        dealer['tel']=info('li:nth-child(3) span.tel').text()
        dealer['salearea']=info('li:nth-child(3) span.sale-whole span.data-business-tip').text()
        dealer['address']=info('li:nth-child(4) span.info-addr').text()
        if info('li:first-child a').attr('href') is None:
            dealer['homepage'] = None
        else:
            dealer['homepage'] = 'https:' + info('li:first-child a').attr('href')
        if  item('a.img-box img').attr('src') is None:
            dealer['image'] =None
        else:
            dealer['image'] = 'https:'+ item('a.img-box img').attr('src')
        dealers.append(dealer)
    return dealers


def get_dealer(city,page=1):
    pageNum=page
    next=True
    allDealers=[]
    while(next):
        #翻页，每次翻页前随机休息几秒
        time.sleep(random.uniform(1,5))
        url=AUTOHOME_URL_DEALER % (city,pageNum)
        print(url)
        heaerData=get_headers()
        result=requests.get(url,headers=heaerData)
        if result.status_code == 200:
            tmp = result.text
            # print(tmp)
            htmldata = pq(tmp)

            # 判断是否最后，#len()=0表示此节点没有获取到，由于PyQuery类型数据，即使没有取到节点，仍然不是None，只能根据len进行判断
            end = htmldata('div.no-result')
            # print(end)
            if end.__len__()==0:
                pageNum +=1
            else:
                next = False

            #解析数据
            data = parse(city,htmldata)
            print(data)
            allDealers.extend(data)
        else:
            return None
    print("================get data finished!!")
    # print(allDealers)
    return allDealers



def clearDealer(dealerTable,con):
    sql='truncate table %s' % dealerTable
    dbutil.exeSql(sql, con)


def saveDealer(data, table):
    connection = dbutil.connectDB('lcb_mars_db')
    cursor = connection.cursor()
    for item in data:
        # print(item)
        insertsql= "insert into %s " \
            "(id,name,medal,brand,tel,salearea,city,address,homepage,image)" \
            "values (%s,'%s','%s','%s','%s','%s','%s','%s','%s','%s')" \
            % (table, item['id'],item['name'],item['medal'],item['brand'],item['tel'],item['salearea'],item['city'],item['address'],item['homepage'] or '',item['image'] or '')
        print(insertsql)
        try:
            cursor.execute(insertsql)
        except Exception as err:
            print(err)
            print('=====>insert sql data failed: '+ insertsql)
            continue

    # cursor.executemany(sql,data)
    connection.commit()
    cursor.close()
    dbutil.closeDB(connection)


def queryDealersFromDB(mysqlDB,dealerTable):
    connection = dbutil.connectDB(mysqlDB)
    cursor = connection.cursor()
    cursor.execute('select id, name, homepage from %s' % dealerTable)
    queryResult=cursor.fetchall()
    dealers=[]
    for row in queryResult:
        dealer={}
        dealer['id']=row[0]
        dealer['name']=row[1]
        dealer['homepage']=row[2]
        dealers.append(dealer)
    cursor.close()
    connection.close()
    return dealers

def updateLcbDealerBrand(con,dealerId,brandId):
    brands = StrAnalyse.loadLcbBrands(con_carbrand,brandId)
    sql = r'update shop_store_withbrand set brand_name="%s" where id="%s"' % (brands,dealerId)
    cursor=con.cursor()
    result=cursor.execute(sql)
    print(sql)
    if result<=0:
        print('=====>execute sql error')
    cursor.close()
    con.commit()


if __name__ == '__main__':
    con_shop = dbutil.connectDB('lcb_db')
    con_carbrand = dbutil.connectDB('lcb_car_db')
    # dealers=StrAnalyse.loadAutoDealers(con_shop,'shop_store_withbrand')
    sql = 'select a.id, a.store_name, a.address, a.lat_gd,a.lon_gd, a.car_brand_type_id,  b.pinyin  from lcb_db.shop_store a left join lcb_comm_db.comm_place b ON a.place_id=b.id where ' \
          'a.store_name not like  "%测试%" and a.store_name  not like "%保监局%" and a.store_name not like "%演示%" and a.store_name not like "%维修%"'
    cursor=con_shop.cursor()
    cursor.execute(sql)
    dealers = cursor.fetchall()
    for dealer in dealers:
        if dealer[5] is None:
            continue
        #更新经纬度
        updateLcbDealerBrand(con_shop,dealer[0],dealer[5])

    cursor.close()
    con_shop.close()
    con_carbrand.close()
